package action.report;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.Map;

import model.app.AppInfo;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.Region;

import service.report.ReportCountService;
import util.Time;
import util.Tool;

import core.action.BaseAction;
import exception.Exceptions;

@SuppressWarnings("deprecation")
public class ReportCountAction extends BaseAction{
	private Map<String, BigDecimal> countMap = new HashMap<String, BigDecimal>();
	private ReportCountService reportCountService;
	
	private AppInfo appInfoName;
	private AppInfo appInfoAddress;
	
	private String dateYear;
	private String dateMonth;
	
	private String searchDate;
	
	private String fileName;
	
	public String execute() throws Exceptions{
		String dateTime = Time.Timestamp2TimeString(Time.CurrentTimestamp());
		dateYear = dateTime.substring(0, 4);
		dateMonth = dateTime.substring(5, 7);
		countMap = this.reportCountService.getReportCountMap(dateTime);
		appInfoName = this.reportCountService.getInfor("companyname");
		appInfoAddress = this.reportCountService.getInfor("area");
		return "success";
	}
	
	/**
	 * 按照时间查询
	 * */
	public String search() throws Exceptions{
		String dateTime = "";
		if(this.searchDate.equals("") || this.searchDate == null){
			dateTime = Time.Timestamp2TimeString(Time.CurrentTimestamp());
		}else{
			dateTime = searchDate;
		}
		
		dateYear = dateTime.substring(0, 4);
		dateMonth = dateTime.substring(5, 7);
		countMap = this.reportCountService.getReportCountMap(dateTime);
		appInfoName = this.reportCountService.getInfor("companyname");
		appInfoAddress = this.reportCountService.getInfor("area");
		return "success";
	}
	
	
	/**
	 * 导出报表,自动调用getInputStream()
	 * */
	public String reportExport(){
		return "report";
	}
	
	public InputStream getInputStream() throws Exceptions {
		Map<String, BigDecimal> countMapList;
        if(this.searchDate.equals("") || this.searchDate == null){
        	String dateTime = Time.Timestamp2TimeString(Time.CurrentTimestamp());
    		dateYear = dateTime.substring(0, 4);
    		dateMonth = dateTime.substring(5, 7);
    		countMapList = this.reportCountService.getReportCountMap(dateTime);
    		appInfoName = this.reportCountService.getInfor("companyname");
    		appInfoAddress = this.reportCountService.getInfor("area");
        }else{
        	String dateTime = searchDate;
    		dateYear = dateTime.substring(0, 4);
    		dateMonth = dateTime.substring(5, 7);
    		countMapList = this.reportCountService.getReportCountMap(dateTime);
    		appInfoName = this.reportCountService.getInfor("companyname");
    		appInfoAddress = this.reportCountService.getInfor("area");
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中  
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中 
		
        HSSFSheet sheet = createSheet(workbook, style);
        addMapRow(sheet, 1, countMapList, style);

		//下载文件名
        fileName=Tool.toChinese(dateYear + "年度" + dateMonth + "月份贷款情况调度汇总表.xls");
        
        //输出下载
		ByteArrayOutputStream baos = new ByteArrayOutputStream();  
		try {
			workbook.write(baos);
		} catch (IOException e) {
			e.printStackTrace();
		}
        byte[] ba = baos.toByteArray();
        ByteArrayInputStream bais = new ByteArrayInputStream(ba);
        return bais;
	}
	public int addMapRow(HSSFSheet sheet, int rowNumber, Map<String, BigDecimal> countMapList, HSSFCellStyle style){
		
		HSSFRow row = sheet.createRow(6);
		for(int i = 0; i < 28; i++){
			row.createCell((short)i).setCellValue(i + 1);
		}
		
		row = sheet.createRow(7);
        row.createCell((short)0).setCellValue(this.appInfoAddress.getValue());
        row.createCell((short)1).setCellValue(this.appInfoName.getValue());
        row.createCell((short)2).setCellValue(countMapList.get("oneHigRate").toString());
        row.createCell((short)3).setCellValue(countMapList.get("oneLowRate").toString());
        row.createCell((short)4).setCellValue(countMapList.get("thisMonAvgRate").toString());
        row.createCell((short)5).setCellValue(countMapList.get("allAvgRate").toString());
        row.createCell((short)6).setCellValue(countMapList.get("atmtm").toString());
        row.createCell((short)7).setCellValue(countMapList.get("allNumber").toString());
        row.createCell((short)8).setCellValue(countMapList.get("acbont").toString());
        row.createCell((short)9).setCellValue(countMapList.get("atmbt").toString());
        row.createCell((short)10).setCellValue(countMapList.get("tmbtt6").toString());
        row.createCell((short)11).setCellValue(countMapList.get("tmbtt7").toString());
        row.createCell((short)12).setCellValue(countMapList.get("tmbtt12").toString());
        row.createCell((short)13).setCellValue(countMapList.get("allNumberN").toString());
        row.createCell((short)14).setCellValue(countMapList.get("cbont").toString());
        row.createCell((short)15).setCellValue(countMapList.get("tmbotn").toString());
        row.createCell((short)16).setCellValue(countMapList.get("allNumberE").toString());
        row.createCell((short)17).setCellValue(countMapList.get("cboet").toString());
        row.createCell((short)18).setCellValue(countMapList.get("tmbote").toString());
        row.createCell((short)19).setCellValue(countMapList.get("allNumberP").toString());
        row.createCell((short)20).setCellValue(countMapList.get("cbopt").toString());
        row.createCell((short)21).setCellValue(countMapList.get("tmbotp").toString());
        row.createCell((short)22).setCellValue(countMapList.get("tmm").toString());
        row.createCell((short)23).setCellValue(countMapList.get("mbon").toString());
        row.createCell((short)24).setCellValue(countMapList.get("mboe").toString());
        row.createCell((short)25).setCellValue(countMapList.get("mbm50").toString());
        row.createCell((short)26).setCellValue(countMapList.get("mbm100").toString());
        row.createCell((short)27).setCellValue(countMapList.get("hightMoneyOne").toString());
		return rowNumber;
	}
	
	public HSSFSheet createSheet(HSSFWorkbook workbook, HSSFCellStyle style){
		HSSFSheet sheet = workbook.createSheet(dateYear + "年度" + dateMonth + "月份贷款情况调度汇总表");
		
        //合并单元格
		sheet.addMergedRegion(new Region(0, (short)0, 0, (short)27));
        sheet.addMergedRegion(new Region(1, (short)0, 5, (short)0));
        sheet.addMergedRegion(new Region(1, (short)1, 5, (short)1));
        sheet.addMergedRegion(new Region(1, (short)2, 1, (short)5));
        sheet.addMergedRegion(new Region(2, (short)2, 5, (short)2));
        sheet.addMergedRegion(new Region(2, (short)3, 5, (short)3));
        sheet.addMergedRegion(new Region(2, (short)4, 5, (short)4));
        sheet.addMergedRegion(new Region(2, (short)5, 5, (short)5));
        sheet.addMergedRegion(new Region(1, (short)6, 1, (short)21));
        sheet.addMergedRegion(new Region(2, (short)6, 5, (short)6));
        sheet.addMergedRegion(new Region(2, (short)7, 2, (short)21));
        sheet.addMergedRegion(new Region(3, (short)7, 4, (short)9));
        sheet.addMergedRegion(new Region(3, (short)10, 3, (short)12));
        sheet.addMergedRegion(new Region(3, (short)13, 3, (short)21));
        sheet.addMergedRegion(new Region(4, (short)10, 4, (short)10));
        sheet.addMergedRegion(new Region(4, (short)11, 4, (short)11));
        sheet.addMergedRegion(new Region(4, (short)12, 4, (short)12));
        sheet.addMergedRegion(new Region(4, (short)13, 4, (short)15));
        sheet.addMergedRegion(new Region(4, (short)16, 4, (short)18));
        sheet.addMergedRegion(new Region(4, (short)19, 4, (short)21));
        sheet.addMergedRegion(new Region(5, (short)7, 5, (short)7));
        sheet.addMergedRegion(new Region(5, (short)8, 5, (short)8));
        sheet.addMergedRegion(new Region(5, (short)9, 5, (short)9));
        sheet.addMergedRegion(new Region(5, (short)10, 5, (short)10));
        sheet.addMergedRegion(new Region(5, (short)11, 5, (short)11));
        sheet.addMergedRegion(new Region(5, (short)12, 5, (short)12));
        sheet.addMergedRegion(new Region(5, (short)13, 5, (short)13));
        sheet.addMergedRegion(new Region(5, (short)14, 5, (short)14));
        sheet.addMergedRegion(new Region(5, (short)15, 5, (short)15));
        sheet.addMergedRegion(new Region(5, (short)16, 5, (short)16));
        sheet.addMergedRegion(new Region(5, (short)17, 5, (short)17));
        sheet.addMergedRegion(new Region(5, (short)18, 5, (short)18));
        sheet.addMergedRegion(new Region(5, (short)19, 5, (short)19));
        sheet.addMergedRegion(new Region(5, (short)20, 5, (short)20));
        sheet.addMergedRegion(new Region(5, (short)21, 5, (short)21));
        sheet.addMergedRegion(new Region(1, (short)22, 1, (short)27));
        sheet.addMergedRegion(new Region(2, (short)22, 5, (short)22));
        sheet.addMergedRegion(new Region(2, (short)23, 5, (short)23));
        sheet.addMergedRegion(new Region(2, (short)24, 5, (short)24));
        sheet.addMergedRegion(new Region(2, (short)25, 5, (short)25));
        sheet.addMergedRegion(new Region(2, (short)26, 5, (short)26));
        sheet.addMergedRegion(new Region(2, (short)27, 5, (short)27));
        
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = null;
        header = sheet.createRow(0);
        cell = header.createCell((short)0);
        cell.setCellValue(new HSSFRichTextString(dateYear + "年度" + dateMonth + "月份贷款情况调度汇总表"));
        cell.setCellStyle(style);
        
        header = sheet.createRow(1);
        cell = header.createCell((short)0);
        cell.setCellValue(new HSSFRichTextString("地区"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)1);
        cell.setCellValue(new HSSFRichTextString("小额贷款公司名称"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)2);
        cell.setCellValue(new HSSFRichTextString("贷款利率"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)22);
        cell.setCellValue(new HSSFRichTextString("贷款余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)6);
        cell.setCellValue(new HSSFRichTextString("贷款发生额"));
        cell.setCellStyle(style);
        
        header = sheet.createRow(2);
        cell = header.createCell((short)2);
        cell.setCellValue(new HSSFRichTextString("单笔最高利率"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)3);
        cell.setCellValue(new HSSFRichTextString("单笔最低利率"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)4);
        cell.setCellValue(new HSSFRichTextString("当月平均利率"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)5);
        cell.setCellValue(new HSSFRichTextString("累计平均利率"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)6);
        cell.setCellValue(new HSSFRichTextString("当月贷款额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)7);
        cell.setCellValue(new HSSFRichTextString("累计贷款额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)22);
        cell.setCellValue(new HSSFRichTextString("当月贷款余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)23);
        cell.setCellValue(new HSSFRichTextString("其中：涉农贷款余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)24);
        cell.setCellValue(new HSSFRichTextString("小微企业贷款余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)25);
        cell.setCellValue(new HSSFRichTextString("单户余额不超过50万的贷款余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)26);
        cell.setCellValue(new HSSFRichTextString("单户余额不超过100万的贷款余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)27);
        cell.setCellValue(new HSSFRichTextString("最高单户贷款余额"));
        cell.setCellStyle(style);
        
        header = sheet.createRow(3);
        cell = header.createCell((short)7);
        cell.setCellValue(new HSSFRichTextString("小计"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)10);
        cell.setCellValue(new HSSFRichTextString("按贷款期限划分"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)13);
        cell.setCellValue(new HSSFRichTextString("按贷款资金投向划分"));
        cell.setCellStyle(style);
        
        header = sheet.createRow(4);
        cell = header.createCell((short)10);
        cell.setCellValue(new HSSFRichTextString("0-6个月"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)11);
        cell.setCellValue(new HSSFRichTextString("7-12个月"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)12);
        cell.setCellValue(new HSSFRichTextString("12个月以上"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)13);
        cell.setCellValue(new HSSFRichTextString("涉农"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)16);
        cell.setCellValue(new HSSFRichTextString("小微企业"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)19);
        cell.setCellValue(new HSSFRichTextString("其它"));
        cell.setCellStyle(style);
        
        header = sheet.createRow(5);
        
        cell = header.createCell((short)7);
        cell.setCellValue(new HSSFRichTextString("户数"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)8);
        cell.setCellValue(new HSSFRichTextString("笔数"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)9);
        cell.setCellValue(new HSSFRichTextString("金额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)10);
        cell.setCellValue(new HSSFRichTextString("金额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)11);
        cell.setCellValue(new HSSFRichTextString("金额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)12);
        cell.setCellValue(new HSSFRichTextString("金额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)13);
        cell.setCellValue(new HSSFRichTextString("户数"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)14);
        cell.setCellValue(new HSSFRichTextString("笔数"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)15);
        cell.setCellValue(new HSSFRichTextString("金额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)16);
        cell.setCellValue(new HSSFRichTextString("户数"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)17);
        cell.setCellValue(new HSSFRichTextString("笔数"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)18);
        cell.setCellValue(new HSSFRichTextString("金额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)19);
        cell.setCellValue(new HSSFRichTextString("户数"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)20);
        cell.setCellValue(new HSSFRichTextString("笔数"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)21);
        cell.setCellValue(new HSSFRichTextString("金额"));
        cell.setCellStyle(style);
        return sheet;
	}

	public Map<String, BigDecimal> getCountMap() {
		return countMap;
	}

	public void setCountMap(Map<String, BigDecimal> countMap) {
		this.countMap = countMap;
	}

	public ReportCountService getReportCountService() {
		return reportCountService;
	}

	public void setReportCountService(ReportCountService reportCountService) {
		this.reportCountService = reportCountService;
	}

	public String getDateYear() {
		return dateYear;
	}

	public void setDateYear(String dateYear) {
		this.dateYear = dateYear;
	}

	public String getDateMonth() {
		return dateMonth;
	}

	public void setDateMonth(String dateMonth) {
		this.dateMonth = dateMonth;
	}

	public String getSearchDate() {
		return searchDate;
	}

	public void setSearchDate(String searchDate) {
		this.searchDate = searchDate;
	}

	public String getFileName() {
		return fileName;
	}

	public void setFileName(String fileName) {
		this.fileName = fileName;
	}

	public AppInfo getAppInfoName() {
		return appInfoName;
	}

	public void setAppInfoName(AppInfo appInfoName) {
		this.appInfoName = appInfoName;
	}

	public AppInfo getAppInfoAddress() {
		return appInfoAddress;
	}

	public void setAppInfoAddress(AppInfo appInfoAddress) {
		this.appInfoAddress = appInfoAddress;
	}
	
}
